﻿using System;
using System.Data.SqlClient;

namespace SelectProductsAndCategoriesFromNorthwind
{
    class ProductAndCategoriesSelector
    {
        static void Main(string[] args)
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
            "Database=Northwind; Integrated Security=true");

            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmdGetAllProducstAndCategories =
                    new SqlCommand("Select cat.CategoryName, prod.ProductName From " +
                        "Products prod left join Categories cat on prod.CategoryID = cat.CategoryID " +
                        "Order by cat.CategoryName",dbCon);
                SqlDataReader reader = cmdGetAllProducstAndCategories.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        string categoryName = (string)reader["CategoryName"];
                        string productName = (string)reader["ProductName"];
                        Console.WriteLine("{0} : {1}", categoryName, productName);
                    }
                }
            }

        }
    }
}
